package com.example.lin.generate;

import com.example.lin.util.DateUtils;
import com.example.lin.util.ExportUtil;
import com.example.lin.util.ImportUtil;
import org.junit.jupiter.api.Test;
import org.springframework.util.StringUtils;
import org.springframework.web.client.RestOperations;
import org.springframework.web.client.RestTemplate;

import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;

/**
 * @className: GenerateEddSql
 * @description: 生成edd脚本信息
 * @author: Lin.Gu
 * @date: 2025/4/14
 */
public class GenerateEddSql {

    public static Map<String, Integer> TIME_ZONE_OFFSETMap = new HashMap<>();
    public static Map<String, Integer> emailMaps = new HashMap<>();
    public static List<String> idList = new ArrayList<>();
    public static Set<String> checkList = new HashSet<>();
    public static Set<String> repeatTheCheckSet = new HashSet<>();
    public static String format2 = "%02d";


    public RestOperations restTemplate = new RestTemplate();

    static {

        emailMaps.put("AU", 1);
        emailMaps.put("CA", 1);
        emailMaps.put("DE", 1);
        emailMaps.put("ES", 1);
        emailMaps.put("FR", 1);
        emailMaps.put("IT", 1);
        emailMaps.put("MX", 1);
        emailMaps.put("GB", 1);
        emailMaps.put("US", 1);


        TIME_ZONE_OFFSETMap.put("AU", -2);
        TIME_ZONE_OFFSETMap.put("CA", 12);
        TIME_ZONE_OFFSETMap.put("DE", 6);
        TIME_ZONE_OFFSETMap.put("ES", 7);
        TIME_ZONE_OFFSETMap.put("FR", 7);
        TIME_ZONE_OFFSETMap.put("IT", 7);
        TIME_ZONE_OFFSETMap.put("MX", 14);
        TIME_ZONE_OFFSETMap.put("MY", 0);
        TIME_ZONE_OFFSETMap.put("NZ", -5);
        TIME_ZONE_OFFSETMap.put("SG", 0);
        TIME_ZONE_OFFSETMap.put("GB", 7);
        TIME_ZONE_OFFSETMap.put("US", 13);
        TIME_ZONE_OFFSETMap.put("IL", 6);

        TIME_ZONE_OFFSETMap.put("MT", 12);
        TIME_ZONE_OFFSETMap.put("LT", 5);
        TIME_ZONE_OFFSETMap.put("DK", 6);
        TIME_ZONE_OFFSETMap.put("NL", 6);
        TIME_ZONE_OFFSETMap.put("HR", 6);
        TIME_ZONE_OFFSETMap.put("EE", 5);
        TIME_ZONE_OFFSETMap.put("SI", 6);
        TIME_ZONE_OFFSETMap.put("CY", 5);
        TIME_ZONE_OFFSETMap.put("SK", 6);
        TIME_ZONE_OFFSETMap.put("BG", 5);
        TIME_ZONE_OFFSETMap.put("AT", 6);
        TIME_ZONE_OFFSETMap.put("HU", 6);
        TIME_ZONE_OFFSETMap.put("LV", 5);
        TIME_ZONE_OFFSETMap.put("BE", 6);
        TIME_ZONE_OFFSETMap.put("PL", 6);
        TIME_ZONE_OFFSETMap.put("RO", 5);
        TIME_ZONE_OFFSETMap.put("LU", 6);
        TIME_ZONE_OFFSETMap.put("CZ", 7);
        TIME_ZONE_OFFSETMap.put("PT", 7);
        TIME_ZONE_OFFSETMap.put("IE", 7);
        TIME_ZONE_OFFSETMap.put("FI", 5);
        TIME_ZONE_OFFSETMap.put("SE", 6);
        TIME_ZONE_OFFSETMap.put("GR", 5);
        TIME_ZONE_OFFSETMap.put("SM", 6);
        TIME_ZONE_OFFSETMap.put("ME", 6);
        TIME_ZONE_OFFSETMap.put("AL", 6);
        TIME_ZONE_OFFSETMap.put("FO", 7);
        TIME_ZONE_OFFSETMap.put("IS", 8);
        TIME_ZONE_OFFSETMap.put("MC", 6);
        TIME_ZONE_OFFSETMap.put("TR", 5);
        TIME_ZONE_OFFSETMap.put("RS", 6);
        TIME_ZONE_OFFSETMap.put("NO", 6);
        TIME_ZONE_OFFSETMap.put("AD", 8);
        TIME_ZONE_OFFSETMap.put("CH", 6);
        TIME_ZONE_OFFSETMap.put("MD", 5);
        TIME_ZONE_OFFSETMap.put("LI", 6);
        TIME_ZONE_OFFSETMap.put("BA", 6);
        TIME_ZONE_OFFSETMap.put("MK", 7);
        TIME_ZONE_OFFSETMap.put("UA", 5);
        TIME_ZONE_OFFSETMap.put("RU", 5);
        TIME_ZONE_OFFSETMap.put("BY", 5);
    }

    private void getId(int count) {
        String forObject = restTemplate.getForObject("http://10.238.234.37:1428/generator/id/" + count, String.class);
        if (!StringUtils.isEmpty(forObject)) {
            //多个参数逗号隔开 转list
            idList = Arrays.asList(forObject.split(","));
        }
    }

    private String getInterceptJvCode(String interceptJvCode) {
        List<String> list = new ArrayList<>();
        String[] split = interceptJvCode.split(",");
        for (String s : split) {
            list.add(s.replace("\"", " ").trim());
        }
        return String.join(",", list);
    }

    private <T> T checkNonNullAndCast(Object object) {
        if (object != null) {
            return (T) object;
        }
        return null;
    }

    private Integer getRandomNum() {
        Random random = new Random();
        // 生成一个 1000 到 9999 之间的随机整数
        int randomNum = random.nextInt(9000) + 1000;
        return randomNum;
    }


    @Test
    public void eddSql() {
        String filePath = "C:\\Users\\181596\\Desktop\\edd配置调整.xlsx";
        int sheet = 0;
        List<List<Object>> list = ImportUtil.readExcelToList(filePath, sheet);
        List<Object> sqlList = new LinkedList<>();
        //主表
        String templateInfo = "INSERT INTO `t_edd_switch_config` (`id`, `project_code`, `service_product_code`, `original_country`, `destination_country`, `vendor_code`, `vendor_service_code`, `time_difference`, `is_rdcout`, `is_email`, `intercept_jv_code`, `start_day_num`, `end_day_num`, `node_count`, `is_enabled`, `is_active`, `create_time`, `create_user_code`, `modify_time`, `modify_user_code`) \n" +
                "VALUES (%s, '%s', '%s', '%s', '%s', '%s', '%s', %s, %s, %s, '%s', %s, %s, %s, 1, 1, NOW(), '181596', NOW(), '181596'); ";
        AtomicReference<String> infoIdPrefix = new AtomicReference<>();
        AtomicReference<String> infoId = new AtomicReference<>();

        //明细表
        String templateDetail = "INSERT INTO `t_edd_switch_detail` (`id`, `edd_config_id`, `node_num`, `jv_index`, `jv_code`, `jv_code_desc`, `sla_aging`, `is_active`, `create_user_code`, `create_time`, `modify_user_code`, `modify_time`) \n" +
                "VALUES (%s, %s, %s, %s, '%s', '%s', %s, 1, '181596', NOW(), '181596', NOW()); ";


        AtomicInteger i = new AtomicInteger();
        AtomicInteger index = new AtomicInteger(1);
        AtomicReference<String> nodeNumValue = new AtomicReference<String>("1");
        list.forEach(e -> {

            String projectCode = (String) e.get(0);
            String serviceId = (String) e.get(1);
            String origional = (String) e.get(2);
            String destination = (String) e.get(3);
            String vendorCode = (String) e.get(4);
            String vendorServiceCode = (String) e.get(5);
            Integer timeDifference = TIME_ZONE_OFFSETMap.get(destination);
            if (timeDifference == null) {
                System.out.println("国家:" + destination + "没有对应时差!");
                return;
            }
            String nodeNum = checkNonNullAndCast(e.get(6));
            String jvCode = checkNonNullAndCast(e.get(7));
            String jvCodeDesc = checkNonNullAndCast(e.get(8));
            String slaAging = checkNonNullAndCast(e.get(9));
            String repeatTheCheck = projectCode + serviceId + origional + destination + vendorCode + vendorServiceCode + nodeNum + jvCode + slaAging;
            if (repeatTheCheckSet.contains(repeatTheCheck)) {
                System.out.println("重复了:" + repeatTheCheck);
                return;
            }
            repeatTheCheckSet.add(repeatTheCheck);


            String dayNum = Arrays.asList("AU", "CA").contains(destination) ? "4" : "2/3";
            Integer startDayNum = null;
            Integer endDayNum = null;
            if (!StringUtils.isEmpty(dayNum)) {
                if (dayNum.contains("/")) {
                    String[] split = dayNum.split("/");
                    startDayNum = Integer.parseInt(split[0]);
                    endDayNum = Integer.parseInt(split[1]);
                } else {
                    startDayNum = Integer.parseInt(dayNum);
                }
            }

            //是否有RDC OUT: 1是 0否  默认1
            String isRdcout = "1";
            //是否邮件告警: 1是 0否
            Integer isEmail = Optional.ofNullable(emailMaps.get(destination)).orElse(0);

            String interceptJvCode = checkNonNullAndCast(e.get(10));
            if (!StringUtils.isEmpty(interceptJvCode)) {
                interceptJvCode = getInterceptJvCode(interceptJvCode);
            }

            String key = projectCode + serviceId + origional + destination + vendorCode + vendorServiceCode;



            if (!checkList.contains(key)) {
                try {
                    Thread.sleep(100);
                } catch (Exception exception) {
                    exception.printStackTrace();
                }

                //初始化
                i.set(0);

                checkList.add(key);
                String id = DateUtils.format(new Date(), DateUtils.YMDHS_FORMAT) + getRandomNum();
                infoIdPrefix.set(id);
                String eddInfoId = id + String.format(format2, i.getAndIncrement());
                infoId.set(eddInfoId);
                //新增主表信息
                String infoFormat = String.format(templateInfo, infoId.get(), projectCode,
                        serviceId, origional, destination, vendorCode, vendorServiceCode,
                        timeDifference, isRdcout, isEmail, interceptJvCode, startDayNum, endDayNum, destination.equals("US") ? 3 : 2);
                //System.out.println(infoFormat);
                sqlList.add(infoFormat);
            }

            if (!nodeNum.equals(nodeNumValue.get())) {
                index.set(1);
            }
            nodeNumValue.set(nodeNum);


            String eddDetailId = infoIdPrefix.get() + String.format(format2, i.getAndIncrement());
            String detailFormat = String.format(templateDetail, eddDetailId, infoId.get(), nodeNum,
                    index.getAndIncrement(), jvCode, jvCodeDesc, slaAging);
            //System.out.println(detailFormat);
            sqlList.add(detailFormat);
        });
        ExportUtil.writeDataToFile(sqlList, "HubEdd配置信息" + sheet);
        System.out.println("完成");
    }
}
